package MySQL;

import Node.Node;
import MainApplication.DateUtils;
import MainApplication.RegisteredUser;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;


public class MySQLAccess {
	private Connection connect = null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;

	public Node n = null;
	private ArrayList<Node> arrayNode;
	private ArrayList<String> arrayUserIds;

	private ArrayList<RegisteredUser> arrayRegisteredUser;

	public MySQLAccess(){
		
	}
	
	public MySQLAccess(String address){
		try{
			Class.forName("com.mysql.jdbc.Driver");
			String connection = "jdbc:mysql://" + address + "/network?";

			connect = DriverManager.getConnection(connection +
			"user=root&password=passw");

			arrayNode = new ArrayList<Node>();
			arrayRegisteredUser = new ArrayList<RegisteredUser>();

		}catch (Exception e){

		}
	}

	public MySQLAccess(Node n){
		this.n = n;

		try{
			Class.forName("com.mysql.jdbc.Driver");
			connect = DriverManager.getConnection("jdbc:mysql://localhost/network?"
					+ "user=root&password=passw");

			preparedStatement = connect.prepareStatement("delete from network.network_table;");
		}catch (Exception e){

		}

	}

	public void deleteAllEntries(){
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connect = DriverManager.getConnection("jdbc:mysql://localhost/network?"
					+ "user=root&password=passw");
			
			statement = connect.createStatement();
			statement.executeUpdate("delete from network.network_table;");
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	public ArrayList<String> readUsersDataBase() throws Exception {
		arrayUserIds = new ArrayList<String>();

		statement = connect.createStatement();
		resultSet = statement
		.executeQuery("select user_id from network.network_table;");

		while(resultSet.next()){
			String user_id = resultSet.getString("user_id");
			arrayUserIds.add(user_id);
		}

		return arrayUserIds;
	}

	public ArrayList<Node> readUsersTemp() throws Exception {
		ArrayList<Node> userTemp = new ArrayList<Node>();

		statement = connect.createStatement();
		resultSet = statement
		.executeQuery("select user_id, temp, time from network.network_table;");

		while(resultSet.next()){
			String user_id = resultSet.getString("user_id");
			String temp = resultSet.getString("temp");
			String time = resultSet.getString("time");

			int index = time.indexOf(" ");

			Node nod = new Node(user_id);
			nod.setTemp(temp);
			nod.setTimestamp(time.substring(index));
			userTemp.add(nod);
		}

		return userTemp;
	}

	public ArrayList<Node> readUsersLight() throws Exception {
		ArrayList<Node> userLight = new ArrayList<Node>();

		statement = connect.createStatement();
		resultSet = statement
		.executeQuery("select user_id, light, time from network.network_table;");

		while(resultSet.next()){
			String user_id = resultSet.getString("user_id");
			String light = resultSet.getString("light");
			String time = resultSet.getString("time");

			int index = time.indexOf(" ");

			Node nod = new Node(user_id);
			nod.setLight(light);
			nod.setTimestamp(time.substring(index));
			userLight.add(nod);
		}

		return userLight;
	}

	public ArrayList<Node> readUsersCurrent() throws Exception {
		ArrayList<Node> userCurrent = new ArrayList<Node>();

		statement = connect.createStatement();
		resultSet = statement
		.executeQuery("select user_id, current, time from network.network_table;");

		while(resultSet.next()){
			String user_id = resultSet.getString("user_id");
			String current = resultSet.getString("current");
			String time = resultSet.getString("time");

			int index = time.indexOf(" ");
			//System.out.println("Timpul este " + time.substring(index));

			Node nod = new Node(user_id);
			nod.setCurrent(current);
			nod.setTimestamp(time.substring(index));
			userCurrent.add(nod);
		}

		return userCurrent;
	}

	public ArrayList<Node> readUsersBattery() throws Exception {
		ArrayList<Node> userBattery = new ArrayList<Node>();

		statement = connect.createStatement();
		resultSet = statement
		.executeQuery("select user_id, battery, time from network.network_table;");

		while(resultSet.next()){
			String user_id = resultSet.getString("user_id");
			String battery = resultSet.getString("battery");
			String time = resultSet.getString("time");

			int index = time.indexOf(" ");
			//System.out.println("Baterie " + battery);

			Node nod = new Node(user_id);
			nod.setBattery(battery);
			nod.setTimestamp(time.substring(index));
			userBattery.add(nod);
		}

		return userBattery;
	}

	public ArrayList<RegisteredUser> readRegisteredUsers() throws Exception {
		try {
			statement = connect.createStatement();
			resultSet = statement
			.executeQuery("select * from network.registeredusers;");
			writeResultSetRegisterUser(resultSet);
		} catch (Exception e){
			throw e;
		}

		return arrayRegisteredUser;
	}

	public ArrayList<Node> readDataBase() throws Exception {
		try {
			statement = connect.createStatement();
			// Result set get the result of the SQL query
			resultSet = statement
			.executeQuery("select * from network.network_table;");

			writeResultSet(resultSet);
		} catch (Exception e) {
			throw e;
		}

		return arrayNode;
	}

	public void insertIntoRegisteredUsers(RegisteredUser ru) throws Exception{
		preparedStatement = connect
		.prepareStatement("insert into  network.registeredusers values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

		preparedStatement.setInt(1, 0);
		preparedStatement.setString(2, ru.username);
		preparedStatement.setString(3, ru.getPassword());
		preparedStatement.setString(4, ru.getFirstName());
		preparedStatement.setString(5, ru.getLastName());
		preparedStatement.setString(6, ru.getMail());

		preparedStatement.setString(7, null);
		preparedStatement.setString(8, null);
		preparedStatement.setString(9, null);
		preparedStatement.setString(10, null);
		preparedStatement.setString(11, null);

		preparedStatement.executeUpdate();

	}

	public void insertIntoTheOtherDatabase() throws Exception{
		// PreparedStatements can use variables and are more efficient
		
		preparedStatement = connect
		.prepareStatement("insert into  network.large_network_table values (?, ?, ?, ?, ?, ?, ?)");

		preparedStatement.setString(1, n.id);
		preparedStatement.setInt(2, n.parent);
		preparedStatement.setString(3, n.getTemp());
		preparedStatement.setString(4, n.getLight());
		preparedStatement.setString(5, n.getBattery());
		preparedStatement.setString(6, n.getCurrent());
		
		Timestamp timestamp = Timestamp.valueOf(DateUtils.now("yyyy-MM-dd HH:mm:ss"));
		//System.out.println(timestamp.toString());
		
		preparedStatement.setTimestamp(7, timestamp);
		
		preparedStatement.executeUpdate();
	}
	
	public void insertIntoDatabase() throws Exception{
		// PreparedStatements can use variables and are more efficient
		preparedStatement = connect
		.prepareStatement("delete from network.network_table where user_id = ?;");

		preparedStatement.setString(1, n.id);
		preparedStatement.executeUpdate();

		preparedStatement = connect
		.prepareStatement("insert into  network.NETWORK_TABLE values (?, ?, ?, ?, ?, ?, ?)");

		preparedStatement.setString(1, n.id);
		preparedStatement.setInt(2, n.parent);
		preparedStatement.setString(3, n.getTemp());
		preparedStatement.setString(4, n.getLight());
		preparedStatement.setString(5, n.getBattery());
		preparedStatement.setString(6, n.getCurrent());
		
		Timestamp timestamp = Timestamp.valueOf(DateUtils.now("yyyy-MM-dd HH:mm:ss"));
		//System.out.println(timestamp.toString());
		
		preparedStatement.setTimestamp(7, timestamp);
		
		preparedStatement.executeUpdate();
	}

	//preia datele din baza de date RegisteredUsers
	private void writeResultSetRegisterUser(ResultSet resultSet) throws SQLException{
		while (resultSet.next()){
			String user = resultSet.getString("user");
			String password = resultSet.getString("password");
			String firstName = resultSet.getString("first_name");
			String lastName = resultSet.getString("last_name");
			String mail = resultSet.getString("mail");
			
			String temp_threshold = resultSet.getString("temp_threshold");
			String light_threshold = resultSet.getString("light_threshold");
			String current_threshold = resultSet.getString("current_threshold");
			String battery_threshold = resultSet.getString("battery_threshold");
			
			RegisteredUser ru = new RegisteredUser(user);

			ru.setFirstName(firstName);
			ru.setLastName(lastName);
			ru.setMail(mail);
			ru.setPassword(password);
			
			ru.temp_threshold = temp_threshold;
			ru.light_threshold = light_threshold;
			ru.current_threshold = current_threshold;
			ru.battery_threshold = battery_threshold;
			
			arrayRegisteredUser.add(ru);
		}
	}

	private void writeResultSet(ResultSet resultSet) throws SQLException {
		// ResultSet is initially before the first data set
		while (resultSet.next()) {
			// It is possible to get the columns via name
			// also possible to get the columns via the column number
			// which starts at 1
			// e.g. resultSet.getSTring(2);
			String user_id = resultSet.getString("user_id");
			int parent = resultSet.getInt("parent");
			String temp = resultSet.getString("temp");
			String light = resultSet.getString("light");
			String battery = resultSet.getString("battery");
			String current = resultSet.getString("current");

			Node nod = new Node(user_id, parent);
			nod.setBattery(battery);
			nod.setCurrent(current);
			nod.setLight(light);
			nod.setTemp(temp);

			arrayNode.add(nod);
		}
	}

	private void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (connect != null) {
				connect.close();
			}
		} catch (Exception e) {

		}
	}
}
